﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.OleDb;
using DOAN.DO;
using DOAN.BL;

namespace DOAN.Admin
{
    public partial class QL_Thanhvien : System.Web.UI.Page
    {
        public SqlConnection sqlcon = new SqlConnection("Data source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|trac_nghiem.mdf;Integrated Security=True;user Instance=True");
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["quyen"] != null)
            {
                if ((Session["quyen"].ToString() == "AD") || (Session["quyen"] == null))
                {

                    Response.Redirect("Dangnhap.aspx");

                }
                else
                {
                    if (Session["Tendangnhap"] != null && Session["Tendangnhap"].ToString() != "")
                    {

                        MasterPage mt = this.Master as MasterPage;
                        LinkButton lb = mt.FindControl("LinkButton1") as LinkButton;
                        lb.Visible = false;
                        Label bl = mt.FindControl("lblDnthanhcong") as Label;
                        bl.Visible = true;
                        Label blten = mt.FindControl("lbtendn") as Label;
                        blten.Visible = true;
                        blten.Text = Session["Tendangnhap"].ToString();
                        LinkButton lbDangky = mt.FindControl("lbDangky") as LinkButton;
                        lbDangky.Visible = false;
                        LinkButton lbthoat = mt.FindControl("lbThoat") as LinkButton;
                        lbthoat.Visible = true;

                    }
                    if (!IsPostBack)
                    {
                        naptv();
                        napns();
                        napns2();
                        Nap_Lop();
                    }
                }
            }
            else
            {
                Response.Redirect("LoginAdmin.aspx");
            }
        }
        private void naptv()
        {
            pnTimkiem.Visible = false;
            try
            {
                griThanhvien.DataSource = new ThanhvienBL().GetTV();
                griThanhvien.DataBind();

            }
            catch (Exception ee)
            {
                lblLoi.Text = ee.Message;
            }
            finally
            {
            }
        }

        private void Lay_tv(string ma)//thuc hien chuc nang bind data 
        {
            ThanhvienDO obj = new ThanhvienBL().Select(new ThanhvienDO{Tentaikhoan = ma});
            try
            {
                lblmatv.Text = obj.Tentaikhoan;
                txtTentaikhoan.Text = obj.Hovaten;
                //TxtHoten.Text = Convert.ToString(mBAL.Hienthitv_theoma(ma).Rows[0].ItemArray[6]);
                txtsdt.Text = obj.Lop;
                txtMk.Text = obj.Email;
                rdnam.Checked = obj.Gioitinh == 1 ? true : false;
                rdNu.Checked = obj.Gioitinh == 0 ? true : false;
                TxtHoten.Text = obj.Matkhau;
                // txtEmailtk.Text = Convert.ToString(mBAL.Hienthitv_theoma(ma).Rows[0].ItemArray[4]);
                TxtLop.Text = obj.Sodienthoai;
                string s = obj.Ngaysinh;
                try
                {
                    int ngay = int.Parse(s.Substring(0, s.IndexOf("/")));
                    s = s.Substring(s.IndexOf("/") + 1);
                    int thang = int.Parse(s.Substring(0, s.IndexOf("/")));
                    int nam = int.Parse(s.Substring(s.IndexOf("/") + 1));

                    drNgaysinh.SelectedValue = ngay.ToString();
                    drThangsinh.SelectedValue = thang.ToString();
                    drNamsinh.SelectedValue = nam.ToString();
                }
                catch(Exception e1)
                {
                }
            }

            catch (Exception ee)
            {
                lblLoi.Text = ee.Message;
            }
            finally
            {
            }
        }
        protected void griThanhvien_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            griThanhvien.PageIndex = e.NewPageIndex;
            this.naptv();
            lblLoi.Visible = true;
        }
        protected void griThanhvien_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            string ma = Convert.ToString(e.CommandArgument);
            int result = 0;
            try
            {
                if (e.CommandName == "xoa")
                {
                    result = new ThanhvienBL().Delete(new ThanhvienDO { Tentaikhoan = ma });
                    if (result == 0)
                    {
                        lblLoi.Text = "Xoa khong thang cong!";
                        
                    }
                    else
                    {
                        lblLoi.Text = "Xoa thanh cong !";
                        naptv();
                    }
                }
                if (e.CommandName == "sua")
                {
                    pnThemsv.Visible = false;
                    pnThanhvien.Visible = true;
                    Lay_tv(ma);
                }
            }
            catch (Exception ee)
            {
                lblLoi.Text = ee.Message;
            }
            finally
            {
            }
        }
        private string ngaysinhs()
        {
            string dd = Convert.ToString(drNgaysinh.SelectedItem.Value);
            string mm = Convert.ToString(drThangsinh.SelectedItem.Value);
            string yy = Convert.ToString(drNamsinh.SelectedItem.Value);
            string gach = "/";
            return dd + gach + mm + gach + yy;
        }
        private string ngaysinhs2()
        {
            string dd = Convert.ToString(drNgaysinh2.SelectedItem.Value);
            string mm = Convert.ToString(drThangsinh2.SelectedItem.Value);
            string yy = Convert.ToString(drNamsinh2.SelectedItem.Value);
            string gach = "/";
            return dd + gach + mm + gach + yy;
        }
        private int gioitinh()
        {
            //int gioitinh = Convert.ToInt32(rdGioitinh.SelectedItem.Value);
            int gioitinh = 0;
            if (rdnam.Checked == true)
                gioitinh = 1;
            return gioitinh;

        }
        private int gioitinh2()
        {
            //int gioitinh = Convert.ToInt32(rdGioitinh.SelectedItem.Value);
            int gioitinh2 = 1;
            if (Convert.ToInt32(rdGioitinh2.SelectedItem.Value) == 1)//lay gia tri gioi tinh :nam=1,nu =0
            {
                gioitinh2 = 1;
            }
            if (Convert.ToInt32(rdGioitinh2.SelectedItem.Value) == 2)
            {
                gioitinh2 = 0;

            }
            return gioitinh2;

        }
        private void napns()
        {

            for (int i = 1; i <= 12; i++)
            {
                drThangsinh.Items.Add(i.ToString());
            }
            for (int i = 1; i <= 31; i++)
            {
                drNgaysinh.Items.Add(i.ToString());
            }
            for (int i = 1950; i <= 1995; i++)
            {
                drNamsinh.Items.Add(i.ToString());
            }
        }
        private void napns2()
        {

            for (int i = 1; i <= 12; i++)
            {
                drThangsinh2.Items.Add(i.ToString());
            }
            for (int i = 1; i <= 31; i++)
            {
                drNgaysinh2.Items.Add(i.ToString());
            }
            for (int i = 1950; i <= 1995; i++)
            {
                drNamsinh2.Items.Add(i.ToString());
            }
        }
        protected void btDongy_Click(object sender, EventArgs e)
        {
            ThanhvienDO obj = new ThanhvienBL().Select(new ThanhvienDO{Tentaikhoan=lblmatv.Text});
            int kq = 0;
            int result = 0;
            obj.Tentaikhoan = lblmatv.Text;
            obj.Hovaten = txtTentaikhoan.Text;
            obj.Lop = txtsdt.Text;
            obj.Email = txtMk.Text;
            obj.Gioitinh = rdnam.Checked ? 1 : 0;
            obj.Matkhau = TxtHoten.Text;
            obj.Sodienthoai = TxtLop.Text;
            obj.Ngaysinh = ngaysinhs();
            try
            {
                kq = new ThanhvienBL().Update(obj);
                if (kq == 0)
                {
                    lblLoi.Text = "Update thanh cong !";
                    naptv();
                    pnThanhvien.Visible = false;
                }
                else
                {
                    lblLoi.Text = "Update khong thanh cong !";
                }
            }
            catch (Exception ex)
            {
                lblLoi.Text = ex.Message;
            }
            finally
            {
                obj = null;
            }
        }
        private void Tim_kiem(int trangthai, string tukhoa)
        {
            try
            {
                griThanhvien.DataSource = new ThanhvienBL().TimKiem(trangthai, tukhoa);
                griThanhvien.DataBind();
            }
            catch (Exception ee)
            {
                lblLoi.Text = ee.Message;
            }
            finally
            {
            }
        }
        protected void imgtk_Click(object sender, ImageClickEventArgs e)
        {
            if (txtTimkiem.Text == "")
            {
                lblLoi.Text = "Bạn chưa nhập thông tin tìm kiếm!";
            }
            else
            {
                string ma = txtTimkiem.Text;
                if (rd_tv.SelectedItem.Value == "0")
                {

                    Tim_kiem(0, ma);
                }
                if (rd_tv.SelectedItem.Value == "1")
                {
                    Tim_kiem(1, ma);
                }
            }
        }
        protected void imgRe_Click(object sender, ImageClickEventArgs e)
        {
            Nap_Lop();
            naptv();
            lblLoi.Visible = false;
            pnThanhvien.Visible = false;
            pnThemsv.Visible = false;
        }
        protected void btLamlai_Click(object sender, EventArgs e)
        {
            pnThanhvien.Visible = false;
        }
        protected void imgXemDS_Click(object sender, ImageClickEventArgs e)
        {
            Nap_Lop();
            pnDSSV.Visible = false;
            pnThemsv.Visible = false;
            pnTimkiem.Visible = false;
        }
        protected void btchon_Click(object sender, EventArgs e)
        {

            string lop = drLop.SelectedValue;

            DataTable data = new ThanhvienBL().SelectByLop(lop);
            //  Napmade(magv);
            if (data != null && data.Rows.Count > 0)
            {
                griThanhvien.DataSource = data;
                griThanhvien.DataBind();
                griThanhvien.Visible = true;
                lblLoi.Visible = true;
            }
            else
            {
                lblLoi.Text = "Chưa có dữ liệu!";
                lblLoi.Visible = true;
                griThanhvien.Visible = false;
            }

        }
        protected void imgTimkiem_Click(object sender, ImageClickEventArgs e)
        {
            Nap_Lop();
            pnTimkiem.Visible = true;
            pnDSSV.Visible = true;
        }
        protected void imgThem_Click(object sender, ImageClickEventArgs e)
        {
            Nap_Lop();
            pnTimkiem.Visible = false;
            pnTK.Visible = false;
            pnThemsv.Visible = true;
            pnThanhvien.Visible = false;
            pnDSSV.Visible = true;
        }
        protected void btLuu_them_Click(object sender, EventArgs e)
        {
            ThanhvienDO obj = new ThanhvienDO();
            int kq = 0;
            obj.Tentaikhoan = txttentk2.Text;
            try
            {
                if (new ThanhvienBL().Select(obj).Maquyen != null)
                    lblLoi.Text = "Da ton tai ten dang nhap nay.!!";
                else
                {
                    obj.Hovaten = txthoten2.Text;
                    obj.Matkhau = txtmk2.Text;
                    obj.Lop = txtlop2.Text;
                    obj.Sodienthoai = txtsdt2.Text;
                    obj.Email = txtemail2.Text;
                    obj.Ngaysinh = ngaysinhs2();
                    obj.Gioitinh = gioitinh2();
                    obj.Maquyen = "TV";
                    kq = new ThanhvienBL().Insert(obj);
                    if (kq == 0)
                    {
                        lblLoi.Text = "Thêm thành công!";
                        naptv();
                        //pnYkien.Visible = false;
                        pnThemsv.Visible = false;
                    }
                    else
                    {
                        lblLoi.Text = "Thêm không thành công !";
                    }
                }
            }
            catch (Exception ex)
            {
                lblLoi.Text = ex.Message;
            }
            finally
            {
            }
        }
        protected void btHuy_luu_Click(object sender, EventArgs e)
        {
            pnThemsv.Visible = false;
        }
        private void Nap_Lop()
        {
            DataTable data = new ThanhvienBL().GetAllLop();
            try
            {
                drLop.DataSource = data;
                drLop.DataTextField = "Lop";
                drLop.DataValueField = "Lop";
                drLop.DataBind();

            }
            catch (Exception ee)
            {
                lblLoi.Text = ee.Message;
            }
            finally
            {
                data.Dispose();
            }
        }

        protected void imgImport_Click(object sender, ImageClickEventArgs e)
        {
            pnImport.Visible = true;
            
        }
        private void BindGrid()
        {
            try
            {
                lblError.Visible = false;
                griThanhvien.DataSource = new ThanhvienBL().GetTV();
                griThanhvien.DataBind();
            }
            catch (Exception ex)
            {
                lblError.Visible = true;
                lblError.Text = ex.Message;
            }

        }

        protected void btnImport_Click(object sender, EventArgs e)
        {
                  
        
            string ExcelFilename = FileUpload1.PostedFile.FileName;
            string ExcelFileNameonly = ExcelFilename.Substring(ExcelFilename.LastIndexOf("\\") + 1);
            string FileExt = ExcelFilename.Substring(ExcelFilename.LastIndexOf(".") + 1);
            string Filenamewithoutextn = ExcelFileNameonly.Remove(ExcelFileNameonly.LastIndexOf("."));
            if (FileExt.ToLower() == "xls")
            {
                try
                {
                    FileUpload1.PostedFile.SaveAs(Server.MapPath("Imported Files/") + Filenamewithoutextn + ".xls");
                    string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Imported Files/") + Filenamewithoutextn + ".xls;Extended Properties=Excel 8.0;";

                    OleDbConnection olecon = new OleDbConnection(strConnectionString);

                    int RecordCount = 0;
                    lblMessage.Visible = false;
                    lblError.Visible = false;
                    olecon.Open();
                    OleDbCommand cmd = olecon.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [Sheet1$]";
                    DataSet ds = new DataSet();
                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                    OleDbDataReader dr = cmd.ExecuteReader();

                    sqlcon.Open();
                    //string deletestr = "delete from importdata";
                    //SqlCommand sqlcmd = new SqlCommand(deletestr, sqlcon);
                    //sqlcmd.ExecuteNonQuery();

                    while (dr.Read())
                    {
                        //db.excutequery("delete from Cauhoi where UserID='" + dr[0] + "'", CommandType.Text);
                        //new CauhoiBL().Delete(new CauhoiDO { Macauhoi = dr[0].ToString() });
                        
                        //CauhoiDO obj = new CauhoiDO();

                      //  obj.Mamonhoc = dr[0].ToString();
                       // obj.Noidung = dr[1].ToString();
                       // obj.Dokho = int.Parse(dr[2].ToString());
                       // obj.Chuong = int.Parse(dr[3].ToString());
                       // obj.Image = dr[4].ToString();
                       // obj.Video = dr[5].ToString();
                       // obj.DapanA = dr[6].ToString();
                       // obj.DapanB = dr[7].ToString();
                      //  obj.DapanC = dr[8].ToString();
                      //  obj.DapanD = dr[9].ToString();
                      //  obj.Phuongandung = dr[10].ToString();
                      //  if (new CauhoiBL().Select2(obj).Rows.Count > 0)
                     //   {
                    //        new CauhoiBL().Update(obj);
                    //    }
                    //    else
                   //     {
                  //          new CauhoiBL().Insert(obj);
                  //      }


                        ThanhvienDO obj = new ThanhvienDO();
                        obj.Tentaikhoan= dr[0].ToString();
                        obj.Maquyen = dr[1].ToString();
                        obj.Matkhau = dr[2].ToString();
                        obj.Email = dr[3].ToString();
                        obj.Sodienthoai = dr[4].ToString();
                        obj.Hovaten = dr[5].ToString();
                        obj.Ngaysinh = dr[6].ToString();
                        obj.Gioitinh = int.Parse(dr[7].ToString());
                        obj.Lop = dr[8].ToString();
                        if (new ThanhvienBL().Select2(obj).Rows.Count>0)
                          {
                              new ThanhvienBL().Update(obj);
                          }
                          else
                            {
                                new ThanhvienBL().Insert(obj);
                            }



                        //new CauhoiBL().Insert(obj);
                        RecordCount++;
                        lblMessage.Visible = true;
                        lblMessage.Text = " Thực hiện thành công : " + RecordCount.ToString() + " Bản ghi!";
                        naptv();
                    }
                    dr.Close();
                    olecon.Close();
                    BindGrid();
                    sqlcon.Close();
                }
                catch (Exception ex)
                {
                    lblError.Visible = true;
                    lblError.Text = ex.Message;
                }
            }
        }

        protected void rd_tv_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (rd_tv.SelectedItem.Value == "2")
            {
                pnTimkiem.Visible = true;
                Nap_Lop();
                pnTK.Visible = false;
            }
            else
            {
                pnTK.Visible = true;
                pnTimkiem.Visible = false;
            }
        }
    }
}
